package org.apak.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.apak.control.*;
import org.apak.log.FileLogger;
import org.apak.model.*;

public class RepairServiceDAO {

	private Connection connection;
	private Statement statement;
	String sqlCommand;

	public RepairServiceDAO() {
		try {
			this.connection = DBConnection.getConnection();
			this.statement = this.connection.createStatement();
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.RepairServiceDAO()",
					FileLogger.getStackTrace(e));
		}
	}

	public List<ServiceNote> getListOfEmployeeServiceNotes(int service_order_fk) {
		sqlCommand = "SELECT P.first_name AS firstName, P.last_name AS lastName, res.employee_fk, res.note FROM (SELECT E.person_fk AS person_fk, S.employee_fk, S.note FROM service_note AS S INNER JOIN employee AS E ON E.employee=S.employee_fk WHERE S.service_order_fk="
				+ service_order_fk
				+ " AND S.note_author_type=2) AS res INNER JOIN person AS P ON P.person=res.person_fk;";
		ResultSet resultSet = null;
		ServiceNote serviceNote = null;
		ArrayList<ServiceNote> listOfNotes = new ArrayList<ServiceNote>();
		try {
			resultSet = this.statement.executeQuery(sqlCommand);
			while (resultSet.next()) {
				serviceNote = new ServiceNote();
				serviceNote.setEmployee_fk(resultSet.getInt("employee_fk"));
				serviceNote.setService_order_fk(service_order_fk);
				serviceNote.setNote(resultSet.getString("note"));
				serviceNote.setEmployee_firstname(resultSet.getString("firstName"));
				serviceNote.setEmployee_lastname(resultSet.getString("lastName"));
				listOfNotes.add(serviceNote);
			}
		} catch (Exception e) {
			FileLogger.log(
					"RepairServiceDAO.getServiceTypePrice(String typeName)",
					FileLogger.getStackTrace(e));
		}
		return listOfNotes;
	}

	public String updateEmployeeServiceNote(String note, int employee_fk,
			int service_order_fk) {
		// FileLogger.logMessage(note + " " + Integer.toString(employee_fk) +
		// " " + Integer.toString(service_order_fk));
		java.util.Date date = new java.util.Date();
		Timestamp created = new Timestamp(date.getTime());
		sqlCommand = "UPDATE service_note SET note = '" + note.trim()
				+ "', created = '" + created + "' WHERE employee_fk="
				+ employee_fk + " AND service_order_fk=" + service_order_fk
				+ ";";
		//System.out.println(sqlCommand);
		try {
			this.statement.executeUpdate(sqlCommand);
			return "ok";
		} catch (Exception e) {
			FileLogger.log(
					"RepairServiceDAO.updateEmployeeServiceNoteServiceNote()",
					FileLogger.getStackTrace(e));
			return "fail";
		}
	}
	
	public String updateCustomerServiceNote(String note, int customer_fk,
			int service_order_fk) {
		// FileLogger.logMessage(note + " " + Integer.toString(employee_fk) +
		// " " + Integer.toString(service_order_fk));
		java.util.Date date = new java.util.Date();
		Timestamp created = new Timestamp(date.getTime());
		sqlCommand = "UPDATE service_note SET note = '" + note.trim()
				+ "', created = '" + created + "' WHERE customer_fk="
				+ customer_fk + " AND service_order_fk=" + service_order_fk
				+ ";";
		//System.out.println(sqlCommand);
		try {
			this.statement.executeUpdate(sqlCommand);
			return "ok";
		} catch (Exception e) {
			FileLogger.log(
					"RepairServiceDAO.updateEmployeeServiceNoteServiceNote()",
					FileLogger.getStackTrace(e));
			return "fail";
		}
	}

	public String addEmployeeServiceNote(int employee_fk, int service_order_fk,
			String note) {
		java.util.Date date = new java.util.Date();
		Timestamp created = new Timestamp(date.getTime());
		sqlCommand = "INSERT INTO service_note (employee_fk, service_order_fk, "
				+ "service_device_fk, note_author_type, created, note) "
				+ "VALUES ("
				+ employee_fk
				+ ", "
				+ service_order_fk
				+ ", 2, 2, '" + created + "', '" + note + "');";
		try {
			this.statement.execute(sqlCommand);
			return "ok";
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.addEmployeeServiceNote()",
					FileLogger.getStackTrace(e));
			return "fail";
		}
	}
	
	public String addCustomerServiceNote(int customer_fk, int service_order_fk,
			String note) {
		java.util.Date date = new java.util.Date();
		Timestamp created = new Timestamp(date.getTime());
		sqlCommand = "INSERT INTO service_note (customer_fk, service_order_fk, "
				+ "service_device_fk, note_author_type, created, note) "
				+ "VALUES ("
				+ customer_fk
				+ ", "
				+ service_order_fk
				+ ", 2, 1, '" + created + "', '" + note + "');";
		try {
			this.statement.execute(sqlCommand);
			return "ok";
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.addEmployeeServiceNote()",
					FileLogger.getStackTrace(e));
			return "fail";
		}
	}

	public ServiceNote getEmployeeServiceNote(int employeeID, int serviceOrder) {
		sqlCommand = String
				.format("SELECT * FROM service_note WHERE employee_fk = %d AND service_order_fk = %d",
						employeeID, serviceOrder);
		ResultSet resultSet = null;
		ServiceNote serviceNote = null;
		try {
			resultSet = this.statement.executeQuery(sqlCommand);
			while (resultSet.next()) {
				serviceNote = new ServiceNote();
				serviceNote.setService_note(resultSet.getInt("service_note"));
				serviceNote.setCustomer_fk(resultSet.getInt("customer_fk"));
				serviceNote.setEmployee_fk(resultSet.getInt("employee_fk"));
				serviceNote.setService_order_fk(resultSet
						.getInt("service_order_fk"));
				serviceNote.setService_device_fk(resultSet
						.getInt("service_device_fk"));
				serviceNote.setNote_author_type(resultSet
						.getInt("note_author_type"));
				serviceNote.setCreated(resultSet.getDate("created"));
				serviceNote.setNote(resultSet.getString("note"));
			}
		} catch (Exception e) {
			FileLogger.log(
					"RepairServiceDAO.getServiceTypePrice(String typeName)",
					FileLogger.getStackTrace(e));
		}
		return serviceNote;
	}
	
	public ServiceNote getCustomerServiceNote(int serviceOrder) {
		sqlCommand = String
				.format("SELECT * FROM service_note WHERE note_author_type = 1 AND service_order_fk = %d",
						serviceOrder);
		ResultSet resultSet = null;
		ServiceNote serviceNote = null;
		try {
			resultSet = this.statement.executeQuery(sqlCommand);
			while (resultSet.next()) {
				serviceNote = new ServiceNote();
				serviceNote.setService_note(resultSet.getInt("service_note"));
				serviceNote.setCustomer_fk(resultSet.getInt("customer_fk"));
				serviceNote.setEmployee_fk(resultSet.getInt("employee_fk"));
				serviceNote.setService_order_fk(resultSet
						.getInt("service_order_fk"));
				serviceNote.setService_device_fk(resultSet
						.getInt("service_device_fk"));
				serviceNote.setNote_author_type(resultSet
						.getInt("note_author_type"));
				serviceNote.setCreated(resultSet.getDate("created"));
				serviceNote.setNote(resultSet.getString("note"));
			}
		} catch (Exception e) {
			FileLogger.log(
					"RepairServiceDAO.getServiceTypePrice(String typeName)",
					FileLogger.getStackTrace(e));
		}
		return serviceNote;
	}

	public int getServiceTypePrice(String typeName) {
		sqlCommand = "SELECT service_price FROM service_type WHERE type_name = '"
				+ typeName + "'";
		ResultSet resultSet = null;
		int price = 0;
		try {
			resultSet = this.statement.executeQuery(sqlCommand);
			while (resultSet.next()) {
				price = resultSet.getInt("service_price");
			}
			return price;
		} catch (Exception e) {
			FileLogger.log(
					"RepairServiceDAO.getServiceTypePrice(String typeName)",
					FileLogger.getStackTrace(e));
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				FileLogger
						.log("RepairServiceDAO.getServiceTypePrice(String typeName) - resultSet.close()",
								FileLogger.getStackTrace(e));
			}
			try {
				if (this.statement != null) {
					this.statement.close();
				}
			} catch (SQLException e) {
				FileLogger
						.log("RepairServiceDAO.getServiceTypePrice(String typeName) - statement.close()",
								FileLogger.getStackTrace(e));
			}
		}
		return 0;
	}

	public Device[] getDeviceList() {
		sqlCommand = "SELECT d.device, d.device_type_fk, dt.type_name, d.name, d.reg_no, d.description, d.model, d.manufacturer "
				+ "FROM device d "
				+ "LEFT JOIN device_type dt ON d.device_type_fk = dt.device_type";
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> deviceList = new ArrayList<Object>();
			while (resultset.next()) {
				Device device = new Device(resultset.getInt("device"),
						resultset.getInt("device_type_fk"),
						resultset.getString("type_name"),
						resultset.getString("name"),
						resultset.getString("reg_no"),
						resultset.getString("description"),
						resultset.getString("model"),
						resultset.getString("manufacturer"));
				deviceList.add(device);
			}
			return Arrays.copyOf(listToArray(deviceList), deviceList.size(),
					Device[].class);
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.getDeviceList()",
					FileLogger.getStackTrace(e));
		}
		return null;
	}

	public Device[] getDeviceList(String serviceOrder) {
		sqlCommand = String
				.format("SELECT d.device, d.device_type_fk, dt.type_name, d.name, d.reg_no, d.description, d.model, d.manufacturer "
						+ "FROM service_device sd "
						+ "LEFT JOIN device d ON d.device = sd.device_fk "
						+ "LEFT JOIN device_type dt ON d.device_type_fk = dt.device_type "
						+ "WHERE sd.service_order_fk = %s", serviceOrder);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> deviceList = new ArrayList<Object>();
			while (resultset.next()) {
				Device device = new Device(resultset.getInt("device"),
						resultset.getInt("device_type_fk"),
						resultset.getString("type_name"),
						resultset.getString("name"),
						resultset.getString("reg_no"),
						resultset.getString("description"),
						resultset.getString("model"),
						resultset.getString("manufacturer"));
				deviceList.add(device);
			}
			return Arrays.copyOf(listToArray(deviceList), deviceList.size(),
					Device[].class);
		} catch (Exception e) {
			FileLogger.log(
					"RepairServiceDAO.getDeviceList(String serviceOrder)",
					FileLogger.getStackTrace(e));
		}
		return null;
	}

	public DeviceType[] getDeviceTypeList() {
		sqlCommand = "SELECT * FROM device_type";
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> deviceTypeList = new ArrayList<Object>();
			while (resultset.next()) {
				DeviceType deviceType = new DeviceType(
						resultset.getInt("device_type"),
						resultset.getInt("super_type_fk"),
						resultset.getInt("level"),
						resultset.getString("type_name"));
				deviceTypeList.add(deviceType);
			}
			return groupDeviceTypeList(Arrays.copyOf(
					listToArray(deviceTypeList), deviceTypeList.size(),
					DeviceType[].class));
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.getDeviceTypeList()",
					FileLogger.getStackTrace(e));
		}
		return null;
	}

	public boolean insertDevice(String name, String reg_no, String description,
			String manufacturer, String model, String device_type_fk) {
		sqlCommand = String
				.format("INSERT INTO device (name, reg_no, description, manufacturer, model, device_type_fk) VALUES ('%s', '%s', '%s', '%s', '%s', %s)",
						name, reg_no, description, manufacturer, model,
						device_type_fk);
		// System.out.println(sqlCommand);
		try {
			statement.executeUpdate(sqlCommand);
			return true;
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.insertDevice()",
					FileLogger.getStackTrace(e));
			return false;
		}
	}

	public Device[] searchDevice(DeviceForm deviceForm) {
		sqlCommand = this.getDeviceSearchString(deviceForm);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> deviceList = new ArrayList<Object>();
			while (resultset.next()) {
				Device device = new Device(resultset.getInt("device"),
						resultset.getInt("device_type_fk"),
						resultset.getString("type_name"),
						resultset.getString("name"),
						resultset.getString("reg_no"),
						resultset.getString("description"),
						resultset.getString("model"),
						resultset.getString("manufacturer"));
				deviceList.add(device);
			}
			return Arrays.copyOf(listToArray(deviceList), deviceList.size(),
					Device[].class);
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.searchDevice()",
					FileLogger.getStackTrace(e));
		}
		return null;
	}

	public ServiceRequest[] getServiceRequestList() {
		sqlCommand = "SELECT sr.service_request, sr.customer_fk, CASE WHEN c.subject_type_fk = 1 THEN p.first_name || ' ' || p.last_name WHEN c.subject_type_fk = 2 THEN e.full_name END AS customer_name,"
				+ " sr.created_by, CASE WHEN c2.subject_type_fk = 1 THEN p2.first_name || ' ' || p2.last_name WHEN c2.subject_type_fk = 2 THEN e2.full_name END AS created_by_name,"
				+ "sr.service_desc_by_customer, sr.service_desc_by_employee, sr.service_request_status_type_fk, st.type_name"
				+ " FROM service_request sr"
				+ " LEFT JOIN service_request_status_type st ON sr.service_request_status_type_fk = st.service_request_status_type"
				+ " LEFT JOIN customer c ON sr.customer_fk = c.customer"
				+ " LEFT JOIN person p ON c.subject_fk = p.person AND c.subject_type_fk = 1"
				+ " LEFT JOIN enterprise e ON c.subject_fk = e.enterprise AND c.subject_type_fk = 2"
				+ " LEFT JOIN customer c2 ON sr.created_by = c2.customer"
				+ " LEFT JOIN person p2 ON c2.subject_fk = p2.person AND c2.subject_type_fk = 1"
				+ " LEFT JOIN enterprise e2 ON c2.subject_fk = e2.enterprise AND c2.subject_type_fk = 2";
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> serviceRequestList = new ArrayList<Object>();
			while (resultset.next()) {
				ServiceRequest serviceRequest = new ServiceRequest(
						resultset.getInt("service_request"),
						resultset.getInt("customer_fk"),
						resultset.getString("customer_name"),
						resultset.getInt("created_by"),
						resultset.getString("created_by_name"),
						resultset.getString("service_desc_by_customer"),
						resultset.getString("service_desc_by_employee"),
						resultset.getInt("service_request_status_type_fk"),
						resultset.getString("type_name"));
				serviceRequestList.add(serviceRequest);
			}
			return Arrays.copyOf(listToArray(serviceRequestList),
					serviceRequestList.size(), ServiceRequest[].class);
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.getServiceRequestList()",
					FileLogger.getStackTrace(e));
		}
		return null;
	}

	public ServiceRequest getServiceRequest(String id) {
		sqlCommand = String
				.format("SELECT sr.service_request, sr.customer_fk, CASE WHEN c.subject_type_fk = 1 THEN p.first_name || ' ' || p.last_name WHEN c.subject_type_fk = 2 THEN e.full_name END AS customer_name,"
						+ " sr.created_by, CASE WHEN c2.subject_type_fk = 1 THEN p2.first_name || ' ' || p2.last_name WHEN c2.subject_type_fk = 2 THEN e2.full_name END AS created_by_name,"
						+ "sr.service_desc_by_customer, sr.service_desc_by_employee, sr.service_request_status_type_fk, st.type_name"
						+ " FROM service_request sr"
						+ " LEFT JOIN service_request_status_type st ON sr.service_request_status_type_fk = st.service_request_status_type"
						+ " LEFT JOIN customer c ON sr.customer_fk = c.customer"
						+ " LEFT JOIN person p ON c.subject_fk = p.person AND c.subject_type_fk = 1"
						+ " LEFT JOIN enterprise e ON c.subject_fk = e.enterprise AND c.subject_type_fk = 2"
						+ " LEFT JOIN customer c2 ON sr.created_by = c.customer"
						+ " LEFT JOIN person p2 ON c2.subject_fk = p2.person AND c2.subject_type_fk = 1"
						+ " LEFT JOIN enterprise e2 ON c2.subject_fk = e2.enterprise AND c2.subject_type_fk = 2"
						+ " WHERE sr.service_request = %s", id);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			ServiceRequest serviceRequest = new ServiceRequest(
					resultset.getInt("service_request"),
					resultset.getInt("customer_fk"),
					resultset.getString("customer_name"),
					resultset.getInt("created_by"),
					resultset.getString("created_by_name"),
					resultset.getString("service_desc_by_customer"),
					resultset.getString("service_desc_by_employee"),
					resultset.getInt("service_request_status_type_fk"),
					resultset.getString("type_name"));
			return serviceRequest;
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.getServiceRequest(String id)",
					FileLogger.getStackTrace(e));
		}
		return null;
	}

	public int insertServiceRequest(ServiceRequest serviceRequest, int user) {
		sqlCommand = String
				.format("SELECT insert_service_request(%d, %d, CAST('%s' AS text), CAST('%s' AS text));",
						serviceRequest.getCustomer_fk(),
						user,
						serviceRequest.getService_desc_by_customer(),
						serviceRequest.getService_desc_by_employee());
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			return resultset.getInt("insert_service_request");
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.insertServiceRequest(ServiceRequest serviceRequest)",
							FileLogger.getStackTrace(e));
			return 0;
		}
	}

	public void cancelServiceRequest(int id) {
		sqlCommand = String
				.format("UPDATE service_request SET service_request_status_type_fk = 2 WHERE service_request = %d",
						id);
		try {
			statement.executeUpdate(sqlCommand);
		} catch (Exception e) {
			FileLogger.log("cancelServiceRequest(int id)",
					FileLogger.getStackTrace(e));
		}
	}

	public ServiceOrder getServiceOrder(String id) {
		sqlCommand = String
				.format("SELECT so.service_order, so.so_status_type_fk,"
						+ " st.type_name, so.created_by, so.service_request_fk,"
						+ " so.price_total, so.note, so.created, so.updated"
						+ " FROM service_order so"
						+ " LEFT JOIN so_status_type st ON so.so_status_type_fk = st.so_status_type"
						+ " WHERE so.service_order = %s", id);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			ServiceOrder serviceOrder = new ServiceOrder(
					resultset.getInt("service_order"),
					resultset.getInt("so_status_type_fk"),
					resultset.getString("type_name"),
					resultset.getInt("created_by"),
					resultset.getInt("service_request_fk"),
					resultset.getInt("price_total"),
					resultset.getString("note"), resultset.getDate("created"),
					resultset.getDate("updated"));
			return serviceOrder;
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.getServiceOrder(String id)",
					FileLogger.getStackTrace(e));
			return null;
		}
	}

	public ServiceOrder getServiceOrderByServiceRequest(String serviceRequest) {
		sqlCommand = String
				.format("SELECT so.service_order, so.so_status_type_fk,"
						+ " st.type_name, so.created_by, so.service_request_fk,"
						+ " so.price_total, so.note, so.created, so.updated"
						+ " FROM service_order so"
						+ " LEFT JOIN so_status_type st ON so.so_status_type_fk = st.so_status_type"
						+ " WHERE so.service_request_fk = %s", serviceRequest);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			ServiceOrder serviceOrder = new ServiceOrder(
					resultset.getInt("service_order"),
					resultset.getInt("so_status_type_fk"),
					resultset.getString("type_name"),
					resultset.getInt("created_by"),
					resultset.getInt("service_request_fk"),
					resultset.getInt("price_total"),
					resultset.getString("note"), resultset.getDate("created"),
					resultset.getDate("updated"));
			return serviceOrder;
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.getServiceOrderByServiceRequest(String serviceRequest)",
							FileLogger.getStackTrace(e));
			return null;
		}
	}

	public ServiceOrder[] getServiceOrderByCustomerList(int customerID) {
		sqlCommand = String
				.format("SELECT so.service_order, so.so_status_type_fk,"
						+ " st.type_name, so.created_by, so.service_request_fk,"
						+ " so.price_total, so.note, so.created, so.updated"
						+ " FROM service_order so"
						+ " INNER JOIN so_status_type st ON so.so_status_type_fk = st.so_status_type"
						+ " INNER JOIN service_request sr ON so.service_request_fk = sr.service_request"
						+ " WHERE sr.customer_fk = %s", customerID);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> serviceOrderList = new ArrayList<Object>();
			while (resultset.next()) {
				ServiceOrder serviceOrder = new ServiceOrder(
						resultset.getInt("service_order"),
						resultset.getInt("so_status_type_fk"),
						resultset.getString("type_name"),
						resultset.getInt("created_by"),
						resultset.getInt("service_request_fk"),
						resultset.getInt("price_total"),
						resultset.getString("note"),
						resultset.getDate("created"),
						resultset.getDate("updated"));
				serviceOrderList.add(serviceOrder);
			}
			return Arrays.copyOf(listToArray(serviceOrderList),
					serviceOrderList.size(), ServiceOrder[].class);
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.getServiceOrderByCustomerList(int customerID)",
							FileLogger.getStackTrace(e));
		}
		return null;
	}

	public Invoice getInvoiceByServiceOrder(String serviceOrderID) {
		sqlCommand = String
				.format("SELECT i.invoice, i.invoice_status_type_fk, st.type_name, i.service_order_fk,"
						+ " i.customer_fk, i.price_total, i.receiver_name, i.reference_number, i.receiver_accounts, i.description"
						+ " FROM invoice i"
						+ " LEFT JOIN invoice_status_type st ON i.invoice_status_type_fk = st.invoice_status_type"
						+ " WHERE i.service_order_fk = %s", serviceOrderID);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			Invoice invoice = new Invoice(resultset.getInt("invoice"),
					resultset.getInt("invoice_status_type_fk"),
					resultset.getString("type_name"),
					resultset.getInt("service_order_fk"),
					resultset.getInt("customer_fk"),
					resultset.getInt("price_total"),
					resultset.getString("receiver_name"),
					resultset.getString("reference_number"),
					resultset.getString("receiver_accounts"),
					resultset.getString("description"));
			return invoice;
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.getServiceOrderByServiceRequest(String serviceRequest)",
							FileLogger.getStackTrace(e));
			return null;
		}
	}

	public InvoiceRow[] getInvoiceRowList(int invoiceID) {
		sqlCommand = String
				.format("SELECT ir.invoice_row, ir.invoice_fk,"
						+ " ir.service_action_fk, ir.service_part_fk, ir.action_part_description, ir.amount, ir.price_total,"
						+ " ir.unit_type, ir.unit_price, ir.invoice_row_type "
						+ " FROM invoice_row ir" + " WHERE ir.invoice_fk = %d",
						invoiceID);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> invoiceRowList = new ArrayList<Object>();
			while (resultset.next()) {
				InvoiceRow invoiceRow = new InvoiceRow(
						resultset.getInt("invoice_row"),
						resultset.getInt("invoice_fk"),
						resultset.getInt("service_action_fk"),
						resultset.getInt("service_part_fk"),
						resultset.getString("action_part_description"),
						resultset.getInt("amount"),
						resultset.getInt("price_total"),
						resultset.getString("unit_type"),
						resultset.getInt("unit_price"),
						resultset.getInt("invoice_row_type"));
				invoiceRowList.add(invoiceRow);
			}
			return Arrays.copyOf(listToArray(invoiceRowList),
					invoiceRowList.size(), InvoiceRow[].class);
		} catch (Exception e) {
			//System.out.println(e.toString());
			FileLogger
					.log("RepairServiceDAO.getServiceDeviceList(String serviceOrder)",
							FileLogger.getStackTrace(e));
		}
		return null;
	}

	public InvoiceStatusType[] getInvoiceStatusTypeList() {
		sqlCommand = String.format("SELECT *" + " FROM invoice_status_type");
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> invoiceStatusTypeList = new ArrayList<Object>();
			while (resultset.next()) {
				InvoiceStatusType invoiceStatusType = new InvoiceStatusType(
						resultset.getInt("invoice_status_type"),
						resultset.getString("type_name"));
				invoiceStatusTypeList.add(invoiceStatusType);
			}
			return Arrays.copyOf(listToArray(invoiceStatusTypeList),
					invoiceStatusTypeList.size(), InvoiceStatusType[].class);
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.getServiceDeviceList(String serviceOrder)",
							FileLogger.getStackTrace(e));
		}
		return null;
	}

	public ServiceDevice[] getServiceDeviceList(String serviceOrder) {
		sqlCommand = String
				.format("SELECT sd.service_device, sd.service_device_status_type_fk,"
						+ " st.type_name, sd.device_fk, d.name, sd.service_order_fk , sd.service_description"
						+ " FROM service_device sd"
						+ " LEFT JOIN service_device_status_type st ON sd.service_device_status_type_fk = st.service_device_status_type"
						+ " LEFT JOIN device d ON sd.device_fk = d.device"
						+ " WHERE sd.service_order_fk = %s", serviceOrder);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> serviceDeviceList = new ArrayList<Object>();
			while (resultset.next()) {
				ServiceDevice serviceDevice = new ServiceDevice(
						resultset.getInt("service_device"),
						resultset.getInt("service_device_status_type_fk"),
						resultset.getString("type_name"),
						resultset.getInt("device_fk"),
						resultset.getString("name"),
						resultset.getInt("service_order_fk"),
						resultset.getString("service_description"));
				serviceDeviceList.add(serviceDevice);
			}
			return Arrays.copyOf(listToArray(serviceDeviceList),
					serviceDeviceList.size(), ServiceDevice[].class);
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.getServiceDeviceList(String serviceOrder)",
							FileLogger.getStackTrace(e));
		}
		return null;
	}

	public ServiceDevice getServiceDevice(String id) {
		sqlCommand = String
				.format("SELECT sd.service_device, sd.service_device_status_type_fk, st.type_name,"
						+ " sd.device_fk, d.name, sd.service_order_fk, sd.service_description"
						+ " FROM service_device sd"
						+ " LEFT JOIN service_device_status_type st ON sd.service_device_status_type_fk = st.service_device_status_type"
						+ " LEFT JOIN device d ON sd.device_fk = d.device"
						+ " WHERE sd.service_device = %s", id);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			ServiceDevice serviceDevice = new ServiceDevice(
					resultset.getInt("service_device"),
					resultset.getInt("service_device_status_type_fk"),
					resultset.getString("type_name"),
					resultset.getInt("device_fk"), resultset.getString("name"),
					resultset.getInt("service_order_fk"),
					resultset.getString("service_description"));
			return serviceDevice;
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.getServiceDevice(String id)",
					e.toString());
			return null;
		}
	}

	public ServicePart[] getServicePartList(String serviceOrder,
			String serviceDevice) {
		sqlCommand = String
				.format("SELECT sp.service_part, sp.service_order_fk, sp.service_device_fk, sp.part_name, sp.serial_no, sp.part_count, sp.part_price, sp.created_by"
						+ " FROM service_part sp"
						+ " WHERE sp.service_order_fk = %s AND sp.service_device_fk = %s",
						serviceOrder, serviceDevice);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> servicePartList = new ArrayList<Object>();
			while (resultset.next()) {
				ServicePart servicePart = new ServicePart(
						resultset.getInt("service_part"),
						resultset.getInt("service_order_fk"),
						resultset.getInt("service_device_fk"),
						resultset.getString("part_name"),
						resultset.getString("serial_no"),
						resultset.getInt("part_count"),
						resultset.getInt("part_price"),
						resultset.getInt("created_by"));
				servicePartList.add(servicePart);
			}
			return Arrays.copyOf(listToArray(servicePartList),
					servicePartList.size(), ServicePart[].class);
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.getServicePartList(String serviceOrder, String serviceDevice)",
							FileLogger.getStackTrace(e));
		}
		return null;
	}

	public ServicePart[] getServicePartList(String serviceOrder) {
		sqlCommand = String
				.format("SELECT sp.service_part, sp.service_order_fk, sp.service_device_fk, sp.part_name, sp.serial_no, sp.part_count, sp.part_price, sp.created_by"
						+ " FROM service_part sp"
						+ " WHERE sp.service_order_fk = %s AND sp.service_device_fk is NULL",
						serviceOrder);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> servicePartList = new ArrayList<Object>();
			while (resultset.next()) {
				ServicePart servicePart = new ServicePart(
						resultset.getInt("service_part"),
						resultset.getInt("service_order_fk"),
						resultset.getInt("service_device_fk"),
						resultset.getString("part_name"),
						resultset.getString("serial_no"),
						resultset.getInt("part_count"),
						resultset.getInt("part_price"),
						resultset.getInt("created_by"));
				servicePartList.add(servicePart);
			}
			return Arrays.copyOf(listToArray(servicePartList),
					servicePartList.size(), ServicePart[].class);
		} catch (Exception e) {
			FileLogger.log(
					"RepairServiceDAO.getServicePartList(String serviceOrder)",
					FileLogger.getStackTrace(e));
		}
		return null;
	}
	
	public ServicePart[] getServicePartListAll(String serviceOrder) {
		sqlCommand = String
				.format("SELECT sp.service_part, sp.service_order_fk, sp.service_device_fk, sp.part_name, sp.serial_no, sp.part_count, sp.part_price, sp.created_by"
						+ " FROM service_part sp"
						+ " WHERE sp.service_order_fk = %s",
						serviceOrder);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> servicePartList = new ArrayList<Object>();
			while (resultset.next()) {
				ServicePart servicePart = new ServicePart(
						resultset.getInt("service_part"),
						resultset.getInt("service_order_fk"),
						resultset.getInt("service_device_fk"),
						resultset.getString("part_name"),
						resultset.getString("serial_no"),
						resultset.getInt("part_count"),
						resultset.getInt("part_price"),
						resultset.getInt("created_by"));
				servicePartList.add(servicePart);
			}
			return Arrays.copyOf(listToArray(servicePartList),
					servicePartList.size(), ServicePart[].class);
		} catch (Exception e) {
			FileLogger.log(
					"RepairServiceDAO.getServicePartList(String serviceOrder)",
					FileLogger.getStackTrace(e));
		}
		return null;
	}

	public ServicePart getServicePart(String id) {
		sqlCommand = String
				.format("SELECT sp.service_part, sp.service_order_fk, sp.service_device_fk, sp.part_name, sp.serial_no, sp.part_count, sp.part_price, sp.created_by "
						+ " FROM service_part sp"
						+ " WHERE sp.service_part = %s", id);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			ServicePart servicePart = new ServicePart(
					resultset.getInt("service_part"),
					resultset.getInt("service_order_fk"),
					resultset.getInt("service_device_fk"),
					resultset.getString("part_name"),
					resultset.getString("serial_no"),
					resultset.getInt("part_count"),
					resultset.getInt("part_price"),
					resultset.getInt("created_by"));
			return servicePart;
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.getServicePart(String id)",
					FileLogger.getStackTrace(e));
			return null;
		}
	}

	public ServiceAction getServiceAction(String id) {
		sqlCommand = String
				.format("SELECT sa.service_action, sa.service_action_status_type_fk,"
						+ " st.type_name AS status_type, sa.service_type_fk, srt.type_name AS service_type,"
						+ " sa.service_device_fk, sa.service_order_fk, sa.service_amount, sa.price,"
						+ " sa.action_description, sa.created_by"
						+ " FROM service_action sa"
						+ " LEFT JOIN service_action_status_type st ON sa.service_action_status_type_fk = st.service_action_status_type"
						+ " LEFT JOIN service_type srt ON sa.service_type_fk = srt.service_type"
						+ " WHERE sa.service_action = %s", id);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			ServiceAction serviceAction = new ServiceAction(
					resultset.getInt("service_action"),
					resultset.getInt("service_action_status_type_fk"),
					resultset.getString("status_type"),
					resultset.getInt("service_type_fk"),
					resultset.getString("service_type"),
					resultset.getInt("service_device_fk"),
					resultset.getInt("service_order_fk"),
					resultset.getInt("service_amount"),
					resultset.getInt("price"),
					resultset.getString("action_description"),
					resultset.getInt("created_by"));
			return serviceAction;
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.getServicePart(String id)",
					e.toString());
			return null;
		}
	}

	public ServiceAction[] getServiceActionList(String serviceOrder,
			String serviceDevice) {
		sqlCommand = String
				.format("SELECT sa.service_action, sa.service_action_status_type_fk,"
						+ " st.type_name AS status_type, sa.service_type_fk, srt.type_name AS service_type,"
						+ " sa.service_device_fk, sa.service_order_fk, sa.service_amount, sa.price,"
						+ " sa.action_description, sa.created_by"
						+ " FROM service_action sa"
						+ " LEFT JOIN service_action_status_type st ON sa.service_action_status_type_fk = st.service_action_status_type"
						+ " LEFT JOIN service_type srt ON sa.service_type_fk = srt.service_type"
						+ " WHERE sa.service_order_fk = %s AND sa.service_device_fk = %s",
						serviceOrder, serviceDevice);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> serviceActionList = new ArrayList<Object>();
			while (resultset.next()) {
				ServiceAction serviceAction = new ServiceAction(
						resultset.getInt("service_action"),
						resultset.getInt("service_action_status_type_fk"),
						resultset.getString("status_type"),
						resultset.getInt("service_type_fk"),
						resultset.getString("service_type"),
						resultset.getInt("service_device_fk"),
						resultset.getInt("service_order_fk"),
						resultset.getInt("service_amount"),
						resultset.getInt("price"),
						resultset.getString("action_description"),
						resultset.getInt("created_by"));
				serviceActionList.add(serviceAction);
			}
			return Arrays.copyOf(listToArray(serviceActionList),
					serviceActionList.size(), ServiceAction[].class);
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.getServiceActionList(String serviceOrder, String serviceDevice)",
							FileLogger.getStackTrace(e));
		}
		return null;
	}

	public ServiceAction[] getServiceActionList(String serviceOrder) {
		sqlCommand = String
				.format("SELECT sa.service_action, sa.service_action_status_type_fk,"
						+ " st.type_name AS status_type, sa.service_type_fk, srt.type_name AS service_type,"
						+ " sa.service_device_fk, sa.service_order_fk, sa.service_amount, sa.price,"
						+ " sa.action_description, sa.created_by"
						+ " FROM service_action sa"
						+ " LEFT JOIN service_action_status_type st ON sa.service_action_status_type_fk = st.service_action_status_type"
						+ " LEFT JOIN service_type srt ON sa.service_type_fk = srt.service_type"
						+ " WHERE sa.service_order_fk = %s AND sa.service_device_fk is NULL",
						serviceOrder);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> serviceActionList = new ArrayList<Object>();
			while (resultset.next()) {
				ServiceAction serviceAction = new ServiceAction(
						resultset.getInt("service_action"),
						resultset.getInt("service_action_status_type_fk"),
						resultset.getString("status_type"),
						resultset.getInt("service_type_fk"),
						resultset.getString("service_type"),
						resultset.getInt("service_device_fk"),
						resultset.getInt("service_order_fk"),
						resultset.getInt("service_amount"),
						resultset.getInt("price"),
						resultset.getString("action_description"),
						resultset.getInt("created_by"));
				serviceActionList.add(serviceAction);
			}
			return Arrays.copyOf(listToArray(serviceActionList),
					serviceActionList.size(), ServiceAction[].class);
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.getServiceActionList(String serviceOrder)",
							FileLogger.getStackTrace(e));
		}
		return null;
	}
	
	public ServiceAction[] getServiceActionListAll(String serviceOrder) {
		sqlCommand = String
				.format("SELECT sa.service_action, sa.service_action_status_type_fk,"
						+ " st.type_name AS status_type, sa.service_type_fk, srt.type_name AS service_type,"
						+ " sa.service_device_fk, sa.service_order_fk, sa.service_amount, sa.price,"
						+ " sa.action_description, sa.created_by"
						+ " FROM service_action sa"
						+ " LEFT JOIN service_action_status_type st ON sa.service_action_status_type_fk = st.service_action_status_type"
						+ " LEFT JOIN service_type srt ON sa.service_type_fk = srt.service_type"
						+ " WHERE sa.service_order_fk = %s",
						serviceOrder);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> serviceActionList = new ArrayList<Object>();
			while (resultset.next()) {
				ServiceAction serviceAction = new ServiceAction(
						resultset.getInt("service_action"),
						resultset.getInt("service_action_status_type_fk"),
						resultset.getString("status_type"),
						resultset.getInt("service_type_fk"),
						resultset.getString("service_type"),
						resultset.getInt("service_device_fk"),
						resultset.getInt("service_order_fk"),
						resultset.getInt("service_amount"),
						resultset.getInt("price"),
						resultset.getString("action_description"),
						resultset.getInt("created_by"));
				serviceActionList.add(serviceAction);
			}
			return Arrays.copyOf(listToArray(serviceActionList),
					serviceActionList.size(), ServiceAction[].class);
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.getServiceActionList(String serviceOrder)",
							FileLogger.getStackTrace(e));
		}
		return null;
	}

	public int updateServiceRequest(ServiceRequest serviceRequest) {
		sqlCommand = String
				.format("UPDATE service_request SET customer_fk = %d,"
						+ " created_by = %d, service_desc_by_customer = '%s', service_desc_by_employee = '%s', service_request_status_type_fk = %d"
						+ " WHERE service_request = %d",
						serviceRequest.getCustomer_fk(),
						serviceRequest.getCreated_by(),
						serviceRequest.getService_desc_by_customer(),
						serviceRequest.getService_desc_by_employee(),
						serviceRequest.getService_request_status_type_fk(),
						serviceRequest.getService_request());
		try {
			statement.executeUpdate(sqlCommand);
			return serviceRequest.getService_request();
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.updateServiceRequest(ServiceRequest serviceRequest)",
							FileLogger.getStackTrace(e));
			return 0;
		}
	}

	public int updateServiceOrderStatus(String status, int serviceOrderID) {
		sqlCommand = String.format(
				"UPDATE service_order SET so_status_type_fk = %s"
						+ "WHERE service_order = %d", status, serviceOrderID);
		try {
			statement.executeUpdate(sqlCommand);
			return serviceOrderID;
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.updateServiceRequest(ServiceRequest serviceRequest)",
							FileLogger.getStackTrace(e));
			return 0;
		}
	}

	public int insertServiceOrder(int serviceRequest, int user) {
		sqlCommand = String
				.format("INSERT INTO service_order(so_status_type_fk, created_by, service_request_fk, updated_by, created, price_total) "
						+ "VALUES(1, %d, %d, %d, now(), 0) RETURNING service_order", user,
						serviceRequest, user);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			return resultset.getInt("service_order");
		} catch (Exception e) {
			FileLogger.log(
					"RepairServiceDAO.insertServiceOrder(int serviceRequest)",
					FileLogger.getStackTrace(e));
			return 0;
		}
	}

	public int insertServiceAction(ServiceActionForm serviceActionForm, int user) {
		if (!serviceActionForm.getService_device_fk().equals("0"))
			sqlCommand = String
					.format("INSERT INTO service_action(service_action_status_type_fk, service_type_fk,"
							+ " service_device_fk, service_order_fk, service_amount, price,"
							+ " action_description, created, created_by) "
							+ "VALUES(%s, %s, %s, %s, '%s', '%s', '%s', now(), %d) RETURNING service_action",
							serviceActionForm
									.getService_action_status_type_fk(),
							serviceActionForm.getService_type_fk(),
							serviceActionForm.getService_device_fk(),
							serviceActionForm.getService_order_fk(),
							serviceActionForm.getService_amount(),
							serviceActionForm.getPrice(), serviceActionForm
									.getAction_description(), user);
		else
			sqlCommand = String
					.format("INSERT INTO service_action(service_action_status_type_fk, service_type_fk,"
							+ " service_order_fk, service_amount, price,"
							+ " action_description, created, created_by) "
							+ "VALUES(%s, %s, %s, '%s', '%s', '%s', now(), %d) RETURNING service_action",
							serviceActionForm
							.getService_action_status_type_fk(),
							serviceActionForm.getService_type_fk(),
							serviceActionForm.getService_order_fk(),
							serviceActionForm.getService_amount(),
							serviceActionForm.getPrice(),
							serviceActionForm.getAction_description(), user);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			int s = resultset.getInt("service_action");
			this.updateServiceOrderTotalPrice(Integer
					.parseInt(serviceActionForm.getService_order_fk()));
			sqlCommand = String
					.format("UPDATE service_order SET updated = now() WHERE service_order = %s",
							serviceActionForm.getService_order_fk());
			statement.executeUpdate(sqlCommand);
			return s;
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.insertServiceAction(ServiceActionForm serviceActionForm)",
							FileLogger.getStackTrace(e));
			return 0;
		}
	}

	public int insertServicePart(ServicePartForm servicePartForm, int user) {
		if (!servicePartForm.getService_device_fk().equals("0"))
			sqlCommand = String
					.format("INSERT INTO service_part(service_order_fk, service_device_fk,"
							+ " part_name, serial_no, part_count, part_price, created, created_by) "
							+ "VALUES(%s, %s, '%s', '%s', '%s', '%s', now(), %d) RETURNING service_part",
							servicePartForm.getService_order(),
							servicePartForm.getService_device_fk(),
							servicePartForm.getPart_name(),
							servicePartForm.getSerial_no(),
							servicePartForm.getPart_count(),
							servicePartForm.getPart_price(), user);
		else
			sqlCommand = String
					.format("INSERT INTO service_part(service_order_fk,"
							+ " part_name, serial_no, part_count, part_price, created, created_by) "
							+ "VALUES(%s, '%s', '%s', '%s', '%s', now(), %d) RETURNING service_part",
							servicePartForm.getService_order(),
							servicePartForm.getPart_name(),
							servicePartForm.getSerial_no(),
							servicePartForm.getPart_count(),
							servicePartForm.getPart_price(), user);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			int s = resultset.getInt("service_part");
			this.updateServiceOrderTotalPrice(Integer.parseInt(servicePartForm
					.getService_order()));
			sqlCommand = String
					.format("UPDATE service_order SET updated = now() WHERE service_order = %s",
							servicePartForm.getService_order());
			statement.executeUpdate(sqlCommand);
			return s;
		} catch (Exception e) {
			//System.out.println(e.toString());
			FileLogger
					.log("RepairServiceDAO.insertServicePart(ServicePartForm servicePartForm)",
							FileLogger.getStackTrace(e));
			return 0;
		}
	}

	public int updateServicePart(ServicePartForm servicePartForm, String id) {
		sqlCommand = String
				.format("UPDATE service_part SET service_order_fk = %s, service_device_fk = %s,"
						+ " part_name = '%s', serial_no = '%s', part_count = %s, part_price = %s"
						+ "WHERE service_part = %s RETURNING service_part",
						servicePartForm.getService_order(),
						servicePartForm.getService_device_fk(),
						servicePartForm.getPart_name(),
						servicePartForm.getSerial_no(),
						servicePartForm.getPart_count(),
						servicePartForm.getPart_price(), id);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			int i = resultset.getInt("service_part");
			this.updateServiceOrderTotalPrice(Integer.parseInt(servicePartForm
					.getService_order()));
			sqlCommand = String
					.format("UPDATE service_order SET updated = now() WHERE service_order = %s",
							servicePartForm.getService_order());
			statement.executeUpdate(sqlCommand);
			return i;
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.updateServicePart(ServicePartForm servicePartForm, String id)",
							FileLogger.getStackTrace(e));
			return 0;
		}
	}

	public int updateInvoice(InvoiceForm invoiceForm) {
		sqlCommand = String
				.format("UPDATE invoice SET invoice_status_type_fk = %s, price_total = %s, customer_fk = %s,"
						+ " receiver_name = '%s', reference_number = '%s', receiver_accounts = '%s', description = '%s' "
						+ "WHERE invoice = %s RETURNING invoice",
						invoiceForm.getInvoice_status_type_fk(),
						invoiceForm.getPrice(), invoiceForm.getCustomer_fk(),
						invoiceForm.getReceiver_name(),
						invoiceForm.getReference_number(),
						invoiceForm.getReceiver_accounts(),
						invoiceForm.getDescription(), invoiceForm.getInvoice());
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			int i = resultset.getInt("invoice");
			this.updateInvoiceTotalPrice(invoiceForm.getInvoice());
			return i;
		} catch (Exception e) {
			// System.out.println(e.toString());
			FileLogger.log(
					"RepairServiceDAO.updateInvoice(InvoiceForm invoiceForm)",
					FileLogger.getStackTrace(e));
			return 0;
		}
	}
	
	
	public int insertInvoice(ServiceOrder serviceOrder) {
		ServiceRequest serviceRequest = this.getServiceRequest(Integer.toString(serviceOrder.getService_request_fk()));
		sqlCommand = String
				.format("INSERT INTO invoice(invoice_status_type_fk, service_order_fk, customer_fk, invoice_date," +
						" due_date, price_total, receiver_name, reference_number, receiver_accounts) " +
						" VALUES(1, %d, %d, now(), now(), %d, '', '', '') RETURNING invoice",
						serviceOrder.getService_order(), serviceRequest.getCustomer_fk(), serviceOrder.getPrice_total());
		//System.out.println("INVOICE - " + sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			int i = resultset.getInt("invoice");
			ServiceAction[] serviceActionArray = this.getServiceActionListAll(Integer.toString(serviceOrder.getService_order()));
			ServicePart[] servicePart = this.getServicePartListAll(Integer.toString(serviceOrder.getService_order()));		
			if(serviceActionArray != null)
				for(int j = 0; j < serviceActionArray.length; j++)
					this.insertInvoiceRow(serviceActionArray[j], i);
			if(servicePart != null)
				for(int j = 0; j < servicePart.length; j++)
					this.insertInvoiceRow(servicePart[j], i);
			return i;
		} catch (Exception e) {
			//System.out.println(e.toString());
			FileLogger.log(
					"RepairServiceDAO.insertInvoice(ServiceOrder serviceOrder, int customerID)",
					FileLogger.getStackTrace(e));
			return 0;
		}
	}
	
	private int insertInvoiceRow(ServiceAction serviceAction, int invoiceID) {
		sqlCommand = String
				.format("INSERT INTO invoice_row(invoice_fk, service_action_fk, action_part_description, amount," +
						" price_total, unit_type, unit_price, invoice_row_type) " +
						" VALUES(%d, %d, '%s', %d, %d, '%s', %d, 2) RETURNING invoice_row",
						invoiceID, serviceAction.getService_action(), serviceAction.getAction_description(),
						serviceAction.getService_amount(), serviceAction.getPrice() * serviceAction.getService_amount(),
						serviceAction.getService_typeName(), serviceAction.getPrice());
		//System.out.println("INVOICE ACTION - " + sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			int i = resultset.getInt("invoice_row");
			return i;
		} catch (Exception e) {
			//System.out.println(e.toString());
			FileLogger.log(
					"RepairServiceDAO.insertInvoiceRow(ServiceAction serviceAction, int invoiceID)",
					FileLogger.getStackTrace(e));
			return 0;
		}
	}
	
	private int insertInvoiceRow(ServicePart servicePart, int invoiceID) {
		sqlCommand = String
				.format("INSERT INTO invoice_row(invoice_fk, service_part_fk, action_part_description, amount," +
						" price_total, unit_type, unit_price, invoice_row_type) " +
						" VALUES(%d, %d, '%s', %d, %d, 'tk.', %d, 1) RETURNING invoice_row",
						invoiceID, servicePart.getService_part(), servicePart.getPart_name() + " " + servicePart.getSerial_no(),
						servicePart.getPart_count(), servicePart.getPart_count() * servicePart.getPart_price(),
						servicePart.getPart_price());
		//System.out.println("INVOICE PART - " + sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			int i = resultset.getInt("invoice_row");
			return i;
		} catch (Exception e) {
			//System.out.println(e.toString());
			FileLogger.log(
					"RepairServiceDAO.insertInvoiceRow(ServiceAction serviceAction, int invoiceID)",
					FileLogger.getStackTrace(e));
			return 0;
		}
	}

	public int updateServiceDevice(ServiceDeviceForm serviceDeviceForm,
			String id) {
		sqlCommand = String
				.format("UPDATE service_device SET service_device_status_type_fk = %s, service_description = '%s',"
						+ " status_changed = now() "
						+ "WHERE service_device = %s RETURNING service_device",
						serviceDeviceForm.getService_device_status_type_fk(),
						serviceDeviceForm.getService_description(), id);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			int i = resultset.getInt("service_device");
			this.updateServiceOrderTotalPrice(Integer
					.parseInt(serviceDeviceForm.getService_order_fk()));
			sqlCommand = String
					.format("UPDATE service_order SET updated = now() WHERE service_order = %s",
							serviceDeviceForm.getService_order_fk());
			statement.executeUpdate(sqlCommand);
			return i;
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.updateServiceDevice(ServiceDeviceForm serviceDeviceForm, String id)",
							FileLogger.getStackTrace(e));
			return 0;
		}
	}

	public int updateServiceAction(ServiceActionForm serviceActionForm,
			String id) {
		// System.out.println("dao act ***" +
		// serviceActionForm.getService_device_fk() + "###");
		if (serviceActionForm.getService_device_fk().equals("0"))
			sqlCommand = String
					.format("UPDATE service_action SET service_action_status_type_fk = %s, service_type_fk = %s,"
							+ " service_device_fk = NULL, service_order_fk = %s, service_amount = %s, price = %s, price_updated = now(), action_description = '%s' "
							+ "WHERE service_action = %s RETURNING service_action",
							serviceActionForm
									.getService_action_status_type_fk(),
							serviceActionForm.getService_type_fk(),
							serviceActionForm.getService_order_fk(),
							serviceActionForm.getService_amount(),
							serviceActionForm.getPrice(), serviceActionForm
									.getAction_description(), id);
		else
			sqlCommand = String
					.format("UPDATE service_action SET service_action_status_type_fk = %s, service_type_fk = %s,"
							+ " service_device_fk = %s, service_order_fk = %s, service_amount = %s, price = %s, price_updated = now(), action_description = '%s' "
							+ "WHERE service_action = %s RETURNING service_action",
							serviceActionForm
									.getService_action_status_type_fk(),
							serviceActionForm.getService_type_fk(),
							serviceActionForm.getService_device_fk(),
							serviceActionForm.getService_order_fk(),
							serviceActionForm.getService_amount(),
							serviceActionForm.getPrice(), serviceActionForm
									.getAction_description(), id);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			int i = resultset.getInt("service_action");
			this.updateServiceOrderTotalPrice(Integer
					.parseInt(serviceActionForm.getService_order_fk()));
			sqlCommand = String
					.format("UPDATE service_order SET updated = now() WHERE service_order = %s",
							serviceActionForm.getService_order_fk());
			statement.executeUpdate(sqlCommand);
			return i;
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.updateServicePart(ServicePartForm servicePartForm, String id)",
							e.toString());
			return 0;
		}
	}

	public boolean insertServiceDevice(String serviceOrder, String device) {
		sqlCommand = String
				.format("INSERT INTO service_device (service_device_status_type_fk, device_fk, service_order_fk, to_store) "
						+ "VALUES ('%s', '%s', '%s', now())", "1", device,
						serviceOrder);
		// System.out.println(sqlCommand);
		try {
			statement.executeUpdate(sqlCommand);
			sqlCommand = String
					.format("UPDATE service_order SET updated = now() WHERE service_order = %s",
							serviceOrder);
			statement.executeUpdate(sqlCommand);
			return true;
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.insertServiceDevice(String serviceOrder, String device)",
							FileLogger.getStackTrace(e));
			return false;
		}
	}

	public ServiceType[] getServiceTypeList() {
		sqlCommand = "SELECT st.service_type, st.service_unit_type_fk, "
				+ "sut.type_name AS unit_name, st.type_name, st.service_price "
				+ "FROM service_type st LEFT JOIN service_unit_type sut ON sut.service_unit_type = st.service_unit_type_fk";
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> serviceTypeList = new ArrayList<Object>();
			while (resultset.next()) {
				ServiceType serviceType = new ServiceType(
						resultset.getInt("service_type"),
						resultset.getInt("service_unit_type_fk"),
						resultset.getString("unit_name"),
						resultset.getString("type_name"),
						resultset.getInt("service_price"));
				serviceTypeList.add(serviceType);
			}
			return Arrays.copyOf(listToArray(serviceTypeList),
					serviceTypeList.size(), ServiceType[].class);
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.getServiceTypeList()",
					FileLogger.getStackTrace(e));
		}
		return null;
	}

	public StatusType[] getStatusTypeList() {
		sqlCommand = "SELECT * " + "FROM so_status_type";
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> statusTypeList = new ArrayList<Object>();
			while (resultset.next()) {
				StatusType statusType = new StatusType(
						resultset.getInt("so_status_type"),
						resultset.getString("type_name"));
				statusTypeList.add(statusType);
			}
			return Arrays.copyOf(listToArray(statusTypeList),
					statusTypeList.size(), StatusType[].class);
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.getStatusTypeList()",
					FileLogger.getStackTrace(e));
		}
		return null;
	}

	public ServiceDeviceStatusType[] getServiceDeviceStatusTypeList() {
		sqlCommand = "SELECT * " + "FROM service_device_status_type";
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> serviceDeviceStatusTypeList = new ArrayList<Object>();
			while (resultset.next()) {
				ServiceDeviceStatusType serviceDeviceStatusType = new ServiceDeviceStatusType(
						resultset.getInt("service_device_status_type"),
						resultset.getString("type_name"));
				serviceDeviceStatusTypeList.add(serviceDeviceStatusType);
			}
			return Arrays.copyOf(listToArray(serviceDeviceStatusTypeList),
					serviceDeviceStatusTypeList.size(),
					ServiceDeviceStatusType[].class);
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.getServiceDeviceStatusTypeList()",
					FileLogger.getStackTrace(e));
		}
		return null;
	}

	public boolean deleteServiceDevice(String id, int serviceOrder) {
		sqlCommand = String.format("DELETE FROM service_device "
				+ "WHERE service_device = %s", id);
		// System.out.println(sqlCommand);
		try {
			statement.executeUpdate(sqlCommand);
			this.updateServiceOrderTotalPrice(serviceOrder);
			sqlCommand = String
					.format("UPDATE service_order SET updated = now() WHERE service_order = %d",
							serviceOrder);
			statement.executeUpdate(sqlCommand);
			return true;
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.deleteServiceDevice(String id)",
					FileLogger.getStackTrace(e));
			return false;
		}
	}

	public boolean deleteServiceAction(String id, int serviceOrder) {
		sqlCommand = String.format("DELETE FROM service_action "
				+ "WHERE service_action = %s", id);
		// System.out.println(sqlCommand);
		try {
			statement.executeUpdate(sqlCommand);
			this.updateServiceOrderTotalPrice(serviceOrder);
			sqlCommand = String
					.format("UPDATE service_order SET updated = now() WHERE service_order = %d",
							serviceOrder);
			statement.executeUpdate(sqlCommand);
			return true;
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.deleteServiceAction(String id)",
					FileLogger.getStackTrace(e));
			return false;
		}
	}

	public boolean deleteServicePart(String id, int serviceOrder) {
		sqlCommand = String.format("DELETE FROM service_part "
				+ "WHERE service_part = %s", id);
		// System.out.println(sqlCommand);
		try {
			statement.executeUpdate(sqlCommand);
			this.updateServiceOrderTotalPrice(serviceOrder);
			sqlCommand = String
					.format("UPDATE service_order SET updated = now() WHERE service_order = %d",
							serviceOrder);
			statement.executeUpdate(sqlCommand);
			return true;
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.deleteServicePart(String id)",
					FileLogger.getStackTrace(e));
			return false;
		}
	}

	public ServiceActionStatusType[] getServiceActionStatusTypeList() {
		sqlCommand = "SELECT * " + "FROM service_action_status_type";
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> serviceActionStatusTypeList = new ArrayList<Object>();
			while (resultset.next()) {
				ServiceActionStatusType serviceActionStatusType = new ServiceActionStatusType(
						resultset.getInt("service_action_status_type"),
						resultset.getString("type_name"));
				serviceActionStatusTypeList.add(serviceActionStatusType);
			}
			return Arrays.copyOf(listToArray(serviceActionStatusTypeList),
					serviceActionStatusTypeList.size(),
					ServiceActionStatusType[].class);
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.getServiceActionStatusTypeList()",
					FileLogger.getStackTrace(e));
		}
		return null;
	}

	public Customer[] getCustomerList() {
		sqlCommand = String
				.format("SELECT c.customer, c.subject_fk, "
						+ "CASE WHEN c.subject_type_fk = 1 THEN p.first_name || ' ' || p.last_name WHEN c.subject_type_fk = 2 THEN e.full_name END AS customer_name, "
						+ "c.subject_type_fk "
						+ "FROM customer c "
						+ "LEFT JOIN person p ON c.subject_fk = p.person AND c.subject_type_fk = 1 "
						+ "LEFT JOIN enterprise e ON c.subject_fk = e.enterprise AND c.subject_type_fk = 2 ");
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> customerList = new ArrayList<Object>();
			while (resultset.next()) {
				Customer customer = new Customer(resultset.getInt("customer"),
						resultset.getInt("subject_fk"),
						resultset.getString("customer_name"),
						resultset.getInt("subject_type_fk"));
				customerList.add(customer);
			}
			return Arrays.copyOf(listToArray(customerList),
					customerList.size(), Customer[].class);
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.getCustomerList()",
					FileLogger.getStackTrace(e));
		}
		return null;
	}

	public Customer[] getCustomerList(String name) {
		sqlCommand = String
				.format("SELECT c.customer, c.subject_fk, "
						+ "CASE WHEN c.subject_type_fk = 1 THEN p.first_name || ' ' || p.last_name WHEN c.subject_type_fk = 2 THEN e.full_name END AS customer_name, "
						+ "c.subject_type_fk "
						+ "FROM customer c "
						+ "LEFT JOIN person p ON c.subject_fk = p.person AND c.subject_type_fk = 1 "
						+ "LEFT JOIN enterprise e ON c.subject_fk = e.enterprise AND c.subject_type_fk = 2 "
						+ "WHERE UPPER(p.first_name) LIKE '%%%s%%' OR UPPER(p.last_name) LIKE '%%%s%%' OR UPPER(e.full_name) LIKE '%%%s%%'",
						name.toUpperCase(), name.toUpperCase(),
						name.toUpperCase());
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			ArrayList<Object> customerList = new ArrayList<Object>();
			while (resultset.next()) {
				Customer customer = new Customer(resultset.getInt("customer"),
						resultset.getInt("subject_fk"),
						resultset.getString("customer_name"),
						resultset.getInt("subject_type_fk"));
				customerList.add(customer);
			}
			return Arrays.copyOf(listToArray(customerList),
					customerList.size(), Customer[].class);
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.getCustomerList(String name)",
					FileLogger.getStackTrace(e));
		}
		return null;
	}

	public Customer getCustomer(String id) {
		sqlCommand = String
				.format("SELECT c.customer, c.subject_fk, "
						+ "CASE WHEN c.subject_type_fk = 1 THEN p.first_name || ' ' || p.last_name WHEN c.subject_type_fk = 2 THEN e.full_name END AS customer_name, "
						+ "c.subject_type_fk "
						+ "FROM customer c "
						+ "LEFT JOIN person p ON c.subject_fk = p.person AND c.subject_type_fk = 1 "
						+ "LEFT JOIN enterprise e ON c.subject_fk = e.enterprise AND c.subject_type_fk = 2 "
						+ "WHERE c.customer = %s", id);
		ResultSet resultset = null;
		Customer customer = null;
		try {
			resultset = this.statement.executeQuery(sqlCommand);
			while (resultset.next()) {
				customer = new Customer(resultset.getInt("customer"),
						resultset.getInt("subject_fk"),
						resultset.getString("customer_name"),
						resultset.getInt("subject_type_fk"));
			}
		} catch (Exception e) {
			FileLogger.log("RepairServiceDAO.getCustomer(String id)",
					FileLogger.getStackTrace(e));
		}
		return customer;
	}

	public LoggedSubject getLoggedSubject(String login, String pass) {
		sqlCommand = String
				.format("SELECT C.customer,UA.user_account, UA.username, P.person, P.first_name, P.last_name FROM customer C INNER "
						+ "JOIN user_account UA ON C.customer = UA.subject_fk  "
						+ "INNER JOIN person P ON C.subject_fk = P.person "
						+ "WHERE C.subject_type_fk = 1 AND UA.subject_type_fk = 4 AND UA.username='%s' AND "
						+ "UA.passw='%s'", login, pass);
		// System.out.println(sqlCommand);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			LoggedSubject subject = new LoggedSubject(
					resultset.getInt("user_account"),
					resultset.getString("username"),
					resultset.getInt("person"),
					resultset.getString("first_name"),
					resultset.getString("last_name"), true,
					resultset.getInt("customer"));
			return subject;
		} catch (Exception e) {
			// System.out.println(e.toString());
			FileLogger.log("RepairServiceDAO.getCustomer(String id)",
					FileLogger.getStackTrace(e));
			resultset = null;
		}
		sqlCommand = String
				.format("SELECT E.employee,UA.user_account, UA.username, P.person, P.first_name, P.last_name FROM employee E INNER "
						+ "JOIN user_account UA ON E.employee = UA.subject_fk  "
						+ "INNER JOIN person P ON E.person_fk = P.person "
						+ "WHERE UA.subject_type_fk = 3 AND UA.username='%s' AND "
						+ "UA.passw='%s'", login, pass);
		// System.out.println(sqlCommand);
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			LoggedSubject subject = new LoggedSubject(
					resultset.getInt("user_account"),
					resultset.getString("username"),
					resultset.getInt("person"),
					resultset.getString("first_name"),
					resultset.getString("last_name"), false,
					resultset.getInt("employee"));
			return subject;
		} catch (Exception e) {
			//System.out.println(e.toString());
			FileLogger.log("RepairServiceDAO.getCustomer(String id)",
					FileLogger.getStackTrace(e));
		}
		return null;
	}

	public int updateInvoiceTotalPrice(int invocieID) {
		sqlCommand = String
				.format("UPDATE invoice SET price_total = (SELECT SUM(price_total)"
						+ " FROM invoice_row WHERE invoice_fk = %d) WHERE invoice = %d RETURNING price_total",
						invocieID, invocieID);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			return resultset.getInt("price_total");
		} catch (Exception e) {
			//System.out.println(e.toString());
			return -1;
		}
	}

	private int updateServiceOrderTotalPrice(int serviceOrderID) {
		sqlCommand = String.format(
				"SELECT update_service_order_total_price(%d);", serviceOrderID);
		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			resultset.next();
			return resultset.getInt("update_service_order_total_price");
		} catch (Exception e) {
			//System.out.println(e.toString());
			return -1;
		}
	}

	private ArrayList<Object> getPersonList(String name,
			ArrayList<Object> personList) {
		sqlCommand = String
				.format("SELECT p.person, p.first_name, p.last_name "
						+ "FROM customer c "
						+ "INNER JOIN person p ON c.subject_fk = p.person AND c.subject_type_fk = 1 "
						+ "WHERE p.first_name = '%s' OR p.last_name = '%s' ",
						name, name);
		// System.out.println(sqlCommand);

		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			while (resultset.next()) {
				Person person = new Person(resultset.getInt("person"),
						resultset.getString("first_name"),
						resultset.getString("last_name"));
				personList.add(person);
			}
			return personList;
			// return Arrays.copyOf(listToArray(personList), personList.size(),
			// Person[].class);
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.getPersonList(String name, ArrayList<Object> personList)",
							FileLogger.getStackTrace(e));
		}
		return null;
	}

	private ArrayList<Object> getEnterpriseList(String name,
			ArrayList<Object> enterpriseList) {
		sqlCommand = String
				.format("SELECT e.enterprise, e.full_name "
						+ "FROM customer c "
						+ "INNER JOIN enterprise e ON c.subject_fk = e.enterprise AND c.subject_type_fk = 2 "
						+ "WHERE e.full_name = '%s' OR e.name = '%s' ", name,
						name);
		// System.out.println(sqlCommand);

		ResultSet resultset;
		try {
			resultset = statement.executeQuery(sqlCommand);
			while (resultset.next()) {
				Enterprise enterprise = new Enterprise(
						resultset.getInt("enterprise"),
						resultset.getString("full_name"));
				enterpriseList.add(enterprise);
			}
			return enterpriseList;
			// return Arrays.copyOf(listToArray(personList), personList.size(),
			// Person[].class);
		} catch (Exception e) {
			FileLogger
					.log("RepairServiceDAO.getEnterpriseList(String name, ArrayList<Object> enterpriseList)",
							FileLogger.getStackTrace(e));
		}
		return null;
	}

	private String getDeviceSearchString(DeviceForm deviceForm) {
		String searchString = "SELECT d.device, d.device_type_fk, dt.type_name, d.name, d.reg_no, d.description, d.model, d.manufacturer "
				+ "FROM device d "
				+ "LEFT JOIN device_type dt ON d.device_type_fk = dt.device_type WHERE 1 = 1";
		if (!deviceForm.getCustomer().equals(""))
			searchString = String
					.format("SELECT d.device, d.device_type_fk, dt.type_name, d.name, d.reg_no, d.description, d.model, d.manufacturer "
							+ "FROM device d LEFT JOIN service_device sd ON d.device = sd.device_fk "
							+ "LEFT JOIN device_type dt ON d.device_type_fk = dt.device_type "
							+ "LEFT JOIN service_order so ON so.service_order = sd.service_order_fk "
							+ "LEFT JOIN service_request sr ON sr.service_request = so.service_request_fk "
							+ "LEFT JOIN customer c ON c.customer = sr.customer_fk "
							+ "LEFT JOIN person p ON c.subject_fk = p.person AND c.subject_type_fk = 1 "
							+ "LEFT JOIN enterprise e ON c.subject_fk = e.enterprise AND c.subject_type_fk = 2"
							+ "WHERE p.first_name LIKE '%%%s%%' OR p.last_name LIKE '%%%s%%' OR e.full_name LIKE '%%%s%%'",
							deviceForm.getCustomer(), deviceForm.getCustomer(),
							deviceForm.getCustomer());
		if (!deviceForm.getName().equals(""))
			searchString += String.format(" AND d.name LIKE '%%%s%%'",
					deviceForm.getName());
		if (!deviceForm.getModel().equals(""))
			searchString += String.format(" AND d.model LIKE '%%%s%%'",
					deviceForm.getModel());
		if (!deviceForm.getReg_no().equals(""))
			searchString += String.format(" AND d.reg_no LIKE '%%%s%%'",
					deviceForm.getReg_no());
		if (!deviceForm.getDevice_type_fk().equals("")
				&& !deviceForm.getDevice_type_fk().equals("0"))
			searchString += String.format(" AND d.device_type_fk = %s",
					deviceForm.getDevice_type_fk());
		//System.out.println(searchString);
		return searchString;
	}

	// private Person getPerson(String id) {
	// //System.out.println("getPersong");
	// sqlCommand =
	// String.format("SELECT c.customer, p.person, p.first_name, p.last_name " +
	// "FROM customer c " +
	// "INNER JOIN person p ON c.subject_fk = p.person AND c.subject_type_fk = 1 "
	// +
	// "WHERE p.person = '%s'", id);
	// ResultSet resultset;
	// try {
	// resultset = statement.executeQuery(sqlCommand);
	// resultset.next();
	// Person person = new Person(
	// resultset.getInt("customer"), resultset.getInt("person"),
	// resultset.getString("first_name"), resultset.getString("last_name")
	// );
	// return person;
	// } catch (Exception e) {
	// System.out.println("private Person getPerson(String id)");
	// }
	// return null;
	// }
	//
	// private Enterprise getEnterprise(String id) {
	// //System.out.println("getEnterprise");
	// sqlCommand =
	// String.format("SELECT c.customer, e.enterprise, e.full_name " +
	// "FROM customer c " +
	// "INNER JOIN enterprise e ON c.subject_fk = e.enterprise AND c.subject_type_fk = 2 "
	// +
	// "WHERE e.enterprise = '%s'", id);
	// ResultSet resultset;
	// try {
	// resultset = statement.executeQuery(sqlCommand);
	// resultset.next();
	// Enterprise enterprise = new Enterprise(
	// resultset.getInt("customer"), resultset.getInt("enterprise"),
	// resultset.getString("full_name")
	// );
	// return enterprise;
	// } catch (Exception e) {
	// System.out.println("private Enterprise getEnterprise(String id)");
	// }
	// return null;
	// }

	private Object[] listToArray(ArrayList<Object> list) {
		Object[] array = new Object[list.size()];
		for (int i = 0; i < list.size(); i++)
			array[i] = list.get(i);
		return array;
	}

	private DeviceType[] groupDeviceTypeList(DeviceType[] deviceTypeList) {
		DeviceType[] groupedDeviceTypeList = new DeviceType[deviceTypeList.length];
		int pos = 0;
		for (int i = 0; i < deviceTypeList.length; i++)
			if (deviceTypeList[i].getLevel() == 1) {
				groupedDeviceTypeList[pos] = deviceTypeList[i];
				pos++;
				for (int j = 0; j < deviceTypeList.length; j++)
					if (deviceTypeList[j].getSuper_type_fk() == deviceTypeList[i]
							.getDevice_type()) {
						groupedDeviceTypeList[pos] = deviceTypeList[j];
						pos++;
					}
			}
		return groupedDeviceTypeList;
	}

	public void closeConnection() {
		try {
			statement.close();
			connection.close();
		} catch (Exception e) {
			statement = null;
			connection = null;
		}
	}

}
